#include "SysDAL.h"
#include <QDebug>
#include <QSqlError>
#include "SysAssist.h"

SysDBL::SysDBL()
{

}


QSqlDatabase SysDBL::SqlConnectClient()
{
    QSqlDatabase db;
    if(QSqlDatabase::contains("qt_sql_default_connection")){
        db = QSqlDatabase::database("qt_sql_default_connection");
    }
    else
    {
#ifdef WIN32
        db=QSqlDatabase::addDatabase("QODBC");
        db.setDatabaseName(QString("DRIVER={SQL SERVER};"
                            "SERVER=%1;" //服务器名称
                            "DATABASE=%2;"//数据库名
                            "UID=%3;"//登录名
                            "PWD=%4;"//密码
                            ).arg("127.0.0.1")
                             .arg("ETRMS")
                             .arg("sa")
                             .arg("@Tan2099"));
#else
        db=QSqlDatabase::addDatabase("QTDS");
        db.setHostName("162.14.100.138");
        db.setDatabaseName("ETRMS");
        db.setPort(1433);
        db.setUserName("sa");
        db.setPassword("@sql2012");
#endif
    }
    return db;
}


//登录功能
ExecStatus SysDBL::Login(QString UserName,QString Passwd)
{
    QSqlQuery query;
    QString lock,dblock;
    ExecStatus LoginStatus;
    QString login=QString("SELECT passWd,Apower FROM Admin where adminId = '%1'").arg(UserName);
    query.setForwardOnly(true);
    bool exeSql = query.exec(login);
    bool isNext = query.next();
    if(exeSql && isNext)
    {
        lock = SysAssist::Encrypt(Passwd);
        dblock = query.value(0).toString();
        if(lock == dblock)
        {
            LoginStatus.Status = true;
            LoginStatus.StatusInfo =  query.value(1).toString();
        }
        else {
            LoginStatus.StatusInfo = "密码错误";
            LoginStatus.Status = false;
        }
    }
    else if(exeSql && !isNext)
    {
        LoginStatus.StatusInfo =  QObject::tr("账号%1不存在").arg(UserName);
        LoginStatus.Status = false;
    }
    else
    {
        LoginStatus.Status = false;
        LoginStatus.StatusInfo = QObject::tr("登录失败，请检查 登录名：%1 密码：%2 \n"
                                       "错误反馈：%3")
                                        .arg(UserName)
                                        .arg(Passwd)
                                        .arg(query.lastError().text());
    }
    qDebug()<<exeSql<<isNext<<"\n"<<lock<<dblock<<LoginStatus.StatusInfo;
    return LoginStatus;
}

//添加管理员
ExecStatus SysDBL::AdminReg(TypeAdmin info)
{
    QVariant add;
    ExecStatus AddStatus;
    add.setValue(info);
    TypeAdmin getInfo=add.value<TypeAdmin>();
    QSqlQuery query;
    QString addSql=QString("insert into Admin"
                           "(adminId,passWd,name,sex,age,idNumber,tel,workDate,Apower)"
                           "values"
                           "(:adminId,:passWd,:name,:sex,:age,:idNumber,:tel,:workDate,:Apower)");
    query.prepare(addSql);
    query.bindValue(":adminId", getInfo.AdminId);	//给每个插入值标识符设定具体值
    query.bindValue(":passWd", SysAssist::Encrypt(getInfo.PassWd));
    query.bindValue(":name", getInfo.Name);
    query.bindValue(":sex", getInfo.Sex);
    query.bindValue(":age", getInfo.Age);
    query.bindValue(":idNumber", getInfo.IdNumber);
    query.bindValue(":tel", getInfo.Tel);
    query.bindValue(":workDate", getInfo.WorkDate);
    query.bindValue(":Apower", getInfo.Power);
    if(query.exec())
    {
        AddStatus.Status = true;
        AddStatus.StatusInfo = nullptr;
    }
    else
    {
        AddStatus.Status = false;
        AddStatus.StatusInfo = query.lastError().text();
    }
    qDebug()<<getInfo.PassWd;
    return AddStatus;
}

//修改管理员信息
ExecStatus SysDBL::AdminModify(TypeAdmin info)
{
    QVariant add;
    ExecStatus UpdateStatus;
    add.setValue(info);
    TypeAdmin getInfo=add.value<TypeAdmin>();
    QSqlQuery query;
    QString upSql=QString("UPDATE Admin SET "
                                  "passWd = :passWd,"
                                    "name = :name,"
                                     "sex = :sex,"
                                     "age = :age,"
                                "idNumber = :idNumber,"
                                     "tel = :tel,"
                                "workDate = :workDate,"
                                  "Apower = :Apower "
                           "WHERE adminId = :adminId ");
    query.prepare(upSql);
    query.bindValue(":adminId", getInfo.AdminId);	//给每个插入值标识符设定具体值
    query.bindValue(":passWd", SysAssist::Encrypt(getInfo.PassWd));
    query.bindValue(":name", getInfo.Name);
    query.bindValue(":sex", getInfo.Sex);
    query.bindValue(":age", getInfo.Age);
    query.bindValue(":idNumber", getInfo.IdNumber);
    query.bindValue(":tel", getInfo.Tel);
    query.bindValue(":workDate", getInfo.WorkDate);
    query.bindValue(":Apower", getInfo.Power);
    if(query.exec())
    {
        UpdateStatus.Status = true;
        UpdateStatus.StatusInfo = nullptr;
    }
    else
    {
        UpdateStatus.Status = false;
        UpdateStatus.StatusInfo = query.lastError().text();
    }
    return UpdateStatus;
}

//删除管理员
ExecStatus SysDBL::AdminRemove(QString ID)
{
    QSqlQuery query;
    ExecStatus DeleteStatus;
    QString deleteCmd="delete from Admin where adminId = :adminId";
    query.setForwardOnly(true);
    query.prepare(deleteCmd);
    query.bindValue(":adminId",ID);
    if(query.exec())
    {
        DeleteStatus.Status = true;
        DeleteStatus.StatusInfo = nullptr;
    }
    else
    {
        DeleteStatus.Status = false;
        DeleteStatus.StatusInfo = query.lastError().text();
    }
    return DeleteStatus;
}

//通过身份证号在花名册中查询该生年级，判断是够满足登记申请条件
QString SysDBL::getStuGrade(QString IdNumber)
{
    QSqlQuery query;
    QString sql=QString("SELECT  stuId FROM  stuInfo where idNumber = %1").arg(IdNumber);
    query.setForwardOnly(true);
    query.exec(sql);
    if(query.next())
    {
        return  query.value(0).toString();

    }
    else
    {
        return query.lastError().text();
    }
}

RegInfo getRegInfo(int classId,QString IdNum)
{
    RegInfo Info;
    QSqlQuery queSeatId,queStu;
    //status查询空位结果
    queSeatId.setForwardOnly(true);
    queStu.setForwardOnly(true);
    bool que1 = queSeatId.exec(QString("SELECT  seatId from seatInfo where status = 0 and classId = %1 ").arg(classId));
    if(que1 && queSeatId.next())
    {
        Info.seatId = queSeatId.value(0).toString();//存入位置id
    }
    queSeatId.finish();
    bool que2 = queStu.exec(QString("SELECT name,stuId,sex FROM stuInfo WHERE idNumber = %1").arg(IdNum));
    if(que2 && queStu.next())
    {
        Info.StuName = queStu.value(0).toString();
        Info.stuId=queStu.value(1).toString();
        Info.StuSex=queStu.value(2).toString();
    }
    qDebug()<<"DAL1:"<<Info.seatId<<Info.seatId.left(3)<<Info.seatId.right(3);
    qDebug()<<"DAL2:"<<Info.StuName<<Info.stuId<<Info.StuSex;
    qDebug()<<"排查："<<queStu.lastError().text();
    queStu.finish();
    return Info;
}


//位置登记
RegInfo SysDBL::StuSeatReg(int classId,QString IdNum,QString Date)
{
    RegInfo stuRegInfo=getRegInfo(classId,IdNum),ReturnInfo;
    QVariant seatReg;
    seatReg.setValue(stuRegInfo);
    RegInfo getInfo=seatReg.value<RegInfo>();
    bool Sid =getInfo.stuId.isEmpty();
    if(Sid)
    {
        ReturnInfo.stuId=nullptr;
        ReturnInfo.StuSex=nullptr;
        ReturnInfo.seatId=nullptr;
        ReturnInfo.StuName=nullptr;
        ReturnInfo.StatusInfo= QObject::tr("在登记信息的过程中发生错误，请联系管理员\n 0851-888888");
        ReturnInfo.Status=false;
    }
    else
    {
        /*

        QString inSql="EXECUTE PRO_SeatReg :seatId,:stuId,:Date,:rtuStatus OUTPUT,:rtuSeatId OUTPUT";
        stuSql.prepare(inSql);
        stuSql.bindValue(":seatId", getInfo.seatId);
        stuSql.bindValue(":stuId", getInfo.stuId);
        stuSql.bindValue(":Date", Date);
        stuSql.bindValue(":rtuStatus",0,QSql::Out);
        stuSql.bindValue(":rtuSeatId",1,QSql::Out);
*/
        QSqlQuery stuSql;
        QString inSql="EXECUTE PRO_SeatReg ?,?,?,? OUTPUT,? OUTPUT";
        stuSql.prepare(inSql);
        stuSql.bindValue(0, getInfo.seatId);
        stuSql.bindValue(1, getInfo.stuId);
        stuSql.bindValue(2, Date);
        stuSql.bindValue(3,0,QSql::Out);
        stuSql.bindValue(4,1,QSql::Out);
        ReturnInfo.stuId=getInfo.stuId;
        ReturnInfo.StuSex=getInfo.StuSex;
        ReturnInfo.seatId=getInfo.seatId;
        ReturnInfo.StuName=getInfo.StuName;
        bool Sexe =stuSql.exec();
        bool PROC_Status=stuSql.boundValue(3).toBool();
        QString Seat = stuSql.boundValue(4).toString();
        if(Sexe)
        {
            if(PROC_Status == true)
            {
                ReturnInfo.StatusInfo = Seat;
                ReturnInfo.Status = PROC_Status;
            }
            else
            {
                ReturnInfo.StatusInfo =  QObject::tr("该同学可能已申请了其他位置,请检查 %1 登记信息").arg(Seat);
                ReturnInfo.Status = PROC_Status;
            }
        }
        else
        {
            ReturnInfo.StatusInfo = stuSql.lastError().text();
            ReturnInfo.Status = false;
        }
        qDebug()<<"StatusInfo:"<<Seat<<"PROC_Return:"<<PROC_Status;
        qDebug()<<"执行情况："<<Sexe;
        qDebug()<<"ReturnInfo.StatusInfo:"<<ReturnInfo.StatusInfo;
        stuSql.finish();
    }
    qDebug()<<Sid;
    return ReturnInfo;
}

//获取位置数量
ClassInfo SysDBL::getSeatNum(int ClassID)
{
    ClassInfo Seat;
    QSqlQuery queClass;
    queClass.setForwardOnly(true);
    queClass.exec(QString("SELECT setAmount,used,empty from classInfo where classId = %1").arg(ClassID));
    bool next=queClass.next();
    if(next)
    {
        Seat.Amount= queClass.value(0).toInt();
        Seat.Used=queClass.value(1).toInt();
        Seat.Empty=queClass.value(2).toInt();
    }
    queClass.finish();
    return Seat;
}



QSqlQueryModel *SysDBL::QueryModelView(QWidget *parent, QString ID)
{
    QSqlQueryModel *query=new QSqlQueryModel(parent);
    QString stu=ID;
    QString str=QString("EXEC PRO_GetBaseRegInfo '%1'").arg(stu);
    query->setQuery(str);
    if (query->lastError().isValid())//当查询语句不正确时，反馈错误，
    {
          qDebug()<<query->lastError().text();
    }
    return query;
}

QSqlQueryModel *SysDBL::QuerySeat(QWidget *parent, QString ID)
{
    QSqlQueryModel *query=new QSqlQueryModel(parent);
    QString stu=ID;
    QString str=QString("EXEC PRO_GetSeatInfo '%1'").arg(stu);
    query->setQuery(str);
    if (query->lastError().isValid())//当查询语句不正确时，反馈错误，
    {
          qDebug()<<query->lastError().text();
    }
    return query;
}
//使用tableWidget显示数据库表
void TableWidgetView(QString sqlCmd, QTableWidget *TableWidget)
{
    int i = 0, j = 0, nColumn, nRow;
    QSqlQuery sql_query;
    sql_query = QSqlQuery(SysDBL::SqlConnectClient());
    sql_query.prepare(sqlCmd);
    sql_query.exec();
    sql_query.last();
    nRow = sql_query.at() + 1;
    TableWidget->setRowCount(nRow);
    nColumn = TableWidget->columnCount();
    sql_query.first();
    while(j<nRow)
    {
        for (i = 0; i<nColumn; i++)
           TableWidget->setItem(j, i, new QTableWidgetItem(sql_query.value(i).toString()));
        j++;
        sql_query.next();
    }
}




//查询登记信息
QStringList SysDBL::regInfo(QString ID)
{
    QStringList Info;
    QSqlQuery query;
    QString StuSql,RegId,StuName,StuSex,StuTel,StuMajor,SeatId,RegDate;
    StuSql = "EXEC PRO_GetBaseRegInfo :StuId";
    query.setForwardOnly(true);
    query.prepare(StuSql);
    query.bindValue(":StuId",ID);
    bool Exe = query.exec();
    bool Next = query.next();
    if(Exe && Next)
    {
          RegId = query.value(0).toString();
        StuName = query.value(1).toString();
         StuSex = query.value(2).toString();
         StuTel = query.value(3).toString();
       StuMajor = query.value(4).toString();
         SeatId = query.value(5).toString();
        RegDate = query.value(6).toString();
        Info<<RegId<<StuName<<StuSex<<StuTel<<StuMajor<<SeatId<<RegDate;
    }
    else
    {
        qDebug()<<query.lastError().text();
    }
    qDebug()<<Exe<<Next;
    return Info;
}

ExecStatus SysDBL::RegRemove(QString id)
{
    QSqlQuery query;
    ExecStatus DeleteStatus;
    QString deleteCmd="EXEC RmoveReg ?";
    query.prepare(deleteCmd);
    query.bindValue(0,id);
    if(query.exec() && query.numRowsAffected()>0)
    {
        DeleteStatus.Status = true;
        DeleteStatus.StatusInfo = nullptr;
    }
    else if(query.exec() && query.numRowsAffected()== 0)
    {
        DeleteStatus.Status = false;
        DeleteStatus.StatusInfo =  QObject::tr("ID不存在，请检查登记id");
    }
    else
    {
        DeleteStatus.Status = false;
        DeleteStatus.StatusInfo = query.lastError().text();
    }
    return DeleteStatus;
}
